package com.lanvideo.dao;

import com.lanvideo.entity.Question;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

/**
 * 题目数据访问层
 */
@Repository
public class QuestionDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 获取所有题目
     */
    public List<Question> findAll() {
        String sql = "SELECT id, content, option_a as optionA, option_b as optionB, option_c as optionC, option_d as optionD, correct_ans as correctAns, type, difficulty, category, teacher_id as teacherId, create_time as createTime FROM question_bank ORDER BY create_time DESC";
        return jdbcTemplate.query(sql, (rs, rowNum) -> {
            Question question = new Question();
            question.setId(rs.getInt("id"));
            question.setContent(rs.getString("content"));
            question.setOptionA(rs.getString("optionA"));
            question.setOptionB(rs.getString("optionB"));
            question.setOptionC(rs.getString("optionC"));
            question.setOptionD(rs.getString("optionD"));
            question.setCorrectAns(rs.getString("correctAns"));
            question.setType(rs.getInt("type"));
            question.setDifficulty(rs.getInt("difficulty"));
            question.setCategory(rs.getString("category"));
            question.setTeacherId(rs.getInt("teacherId"));
            if (rs.getTimestamp("createTime") != null) {
                question.setCreateTime(rs.getTimestamp("createTime").toLocalDateTime());
            }
            return question;
        });
    }

    /**
     * 管理员获取所有题目（包含创建者信息）
     */
    public List<Question> findAllWithTeacherInfo() {
        String sql = "SELECT q.id, q.content, q.option_a as optionA, q.option_b as optionB, q.option_c as optionC, q.option_d as optionD, q.correct_ans as correctAns, q.type, q.difficulty, q.category, q.teacher_id as teacherId, q.create_time as createTime, u.name as teacherName FROM question_bank q LEFT JOIN user u ON q.teacher_id = u.id ORDER BY q.create_time DESC";
        return jdbcTemplate.query(sql, (rs, rowNum) -> {
            Question question = new Question();
            question.setId(rs.getInt("id"));
            question.setContent(rs.getString("content"));
            question.setOptionA(rs.getString("optionA"));
            question.setOptionB(rs.getString("optionB"));
            question.setOptionC(rs.getString("optionC"));
            question.setOptionD(rs.getString("optionD"));
            question.setCorrectAns(rs.getString("correctAns"));
            question.setType(rs.getInt("type"));
            question.setDifficulty(rs.getInt("difficulty"));
            question.setCategory(rs.getString("category"));
            question.setTeacherId(rs.getInt("teacherId"));
            if (rs.getTimestamp("createTime") != null) {
                question.setCreateTime(rs.getTimestamp("createTime").toLocalDateTime());
            }
            question.setTeacherName(rs.getString("teacherName"));
            return question;
        });
    }

    /**
     * 根据ID查找题目
     */
    public Question findById(Integer id) {
        String sql = "SELECT id, content, option_a as optionA, option_b as optionB, option_c as optionC, option_d as optionD, correct_ans as correctAns, type, difficulty, category, teacher_id as teacherId, create_time as createTime FROM question_bank WHERE id = ?";
        List<Question> questions = jdbcTemplate.query(sql, (rs, rowNum) -> {
            Question question = new Question();
            question.setId(rs.getInt("id"));
            question.setContent(rs.getString("content"));
            question.setOptionA(rs.getString("optionA"));
            question.setOptionB(rs.getString("optionB"));
            question.setOptionC(rs.getString("optionC"));
            question.setOptionD(rs.getString("optionD"));
            question.setCorrectAns(rs.getString("correctAns"));
            question.setType(rs.getInt("type"));
            question.setDifficulty(rs.getInt("difficulty"));
            question.setCategory(rs.getString("category"));
            question.setTeacherId(rs.getInt("teacherId"));
            if (rs.getTimestamp("createTime") != null) {
                question.setCreateTime(rs.getTimestamp("createTime").toLocalDateTime());
            }
            return question;
        }, id);
        return questions.isEmpty() ? null : questions.get(0);
    }

    /**
     * 根据ID获取题目（别名方法）
     */
    public Question getQuestionById(Integer id) {
        return findById(id);
    }

    /**
     * 随机获取指定数量的题目
     */
    public List<Question> getRandomQuestions(int count) {
        String sql = "SELECT id, content, option_a as optionA, option_b as optionB, option_c as optionC, option_d as optionD, correct_ans as correctAns, create_time as createTime FROM question_bank ORDER BY RAND() LIMIT ?";
        return jdbcTemplate.query(sql, (rs, rowNum) -> {
            Question question = new Question();
            question.setId(rs.getInt("id"));
            question.setContent(rs.getString("content"));
            question.setOptionA(rs.getString("optionA"));
            question.setOptionB(rs.getString("optionB"));
            question.setOptionC(rs.getString("optionC"));
            question.setOptionD(rs.getString("optionD"));
            question.setCorrectAns(rs.getString("correctAns"));
            question.setDifficulty(1); // 默认难度
            question.setCategory("默认分类"); // 默认分类
            question.setTeacherId(1); // 默认老师ID
            if (rs.getTimestamp("createTime") != null) {
                question.setCreateTime(rs.getTimestamp("createTime").toLocalDateTime());
            }
            return question;
        }, count);
    }

    /**
     * 获取题目总数
     */
    public int getTotalCount() {
        String sql = "SELECT COUNT(*) FROM question_bank";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
        return count != null ? count : 0;
    }

    /**
     * 保存题目
     */
    public int save(Question question) {
        String sql = "INSERT INTO question_bank (content, option_a, option_b, option_c, option_d, correct_ans, type, difficulty, category, teacher_id) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        return jdbcTemplate.update(sql, question.getContent(), question.getOptionA(), 
                question.getOptionB(), question.getOptionC(), question.getOptionD(), question.getCorrectAns(),
                question.getType(), question.getDifficulty(), question.getCategory(), question.getTeacherId());
    }

    /**
     * 更新题目
     */
    public int update(Question question) {
        String sql = "UPDATE question_bank SET content = ?, option_a = ?, option_b = ?, " +
                "option_c = ?, option_d = ?, correct_ans = ?, type = ?, difficulty = ?, category = ? WHERE id = ?";
        return jdbcTemplate.update(sql, question.getContent(), question.getOptionA(), 
                question.getOptionB(), question.getOptionC(), question.getOptionD(), 
                question.getCorrectAns(), question.getType(), question.getDifficulty(), question.getCategory(), question.getId());
    }

    /**
     * 删除题目
     */
    public int deleteById(Integer id) {
        try {
            String sql = "DELETE FROM question_bank WHERE id = ?";
            int result = jdbcTemplate.update(sql, id);
            System.out.println("删除题目SQL执行结果: " + result + ", 题目ID: " + id);
            return result;
        } catch (Exception e) {
            System.err.println("删除题目失败: " + e.getMessage());
            e.printStackTrace();
            throw e;
        }
    }

    /**
     * 创建题目（与ExamService调用方法名匹配）
     */
    public boolean createQuestion(Question question) {
        try {
            return save(question) > 0;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 更新题目（与ExamService调用方法名匹配）
     */
    public boolean updateQuestion(Question question) {
        try {
            return update(question) > 0;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 删除题目（与ExamService调用方法名匹配）
     */
    public boolean deleteQuestion(Integer questionId) {
        try {
            return deleteById(questionId) > 0;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 根据老师ID获取题目列表
     */
    public List<Question> findByTeacherId(Integer teacherId) {
        String sql = "SELECT id, content, option_a as optionA, option_b as optionB, option_c as optionC, option_d as optionD, correct_ans as correctAns, type, difficulty, category, teacher_id as teacherId, create_time as createTime FROM question_bank WHERE teacher_id = ? ORDER BY create_time DESC";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Question.class), teacherId);
    }

    /**
     * 根据分类获取题目列表
     */
    public List<Question> findByCategory(String category) {
        String sql = "SELECT id, content, option_a as optionA, option_b as optionB, option_c as optionC, option_d as optionD, correct_ans as correctAns, type, difficulty, category, teacher_id as teacherId, create_time as createTime FROM question_bank WHERE category = ? ORDER BY create_time DESC";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Question.class), category);
    }

    /**
     * 根据难度获取题目列表
     */
    public List<Question> findByDifficulty(Integer difficulty) {
        String sql = "SELECT id, content, option_a as optionA, option_b as optionB, option_c as optionC, option_d as optionD, correct_ans as correctAns, type, difficulty, category, teacher_id as teacherId, create_time as createTime FROM question_bank WHERE difficulty = ? ORDER BY create_time DESC";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Question.class), difficulty);
    }

    /**
     * 根据题目类型获取题目列表
     */
    public List<Question> findByType(Integer type) {
        String sql = "SELECT id, content, option_a as optionA, option_b as optionB, option_c as optionC, option_d as optionD, correct_ans as correctAns, type, difficulty, category, teacher_id as teacherId, create_time as createTime FROM question_bank WHERE type = ? ORDER BY create_time DESC";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Question.class), type);
    }

    /**
     * 获取所有分类
     */
    public List<String> getAllCategories() {
        String sql = "SELECT DISTINCT category FROM question_bank WHERE category IS NOT NULL ORDER BY category";
        return jdbcTemplate.queryForList(sql, String.class);
    }

    /**
     * 根据条件搜索题目
     */
    public List<Question> searchQuestions(String keyword, String category, Integer difficulty, Integer teacherId) {
        StringBuilder sql = new StringBuilder("SELECT id, content, option_a as optionA, option_b as optionB, option_c as optionC, option_d as optionD, correct_ans as correctAns, difficulty, category, teacher_id as teacherId, create_time as createTime FROM question_bank WHERE 1=1");
        List<Object> params = new ArrayList<>();
        
        if (keyword != null && !keyword.trim().isEmpty()) {
            sql.append(" AND (content LIKE ? OR option_a LIKE ? OR option_b LIKE ? OR option_c LIKE ? OR option_d LIKE ?)");
            String likeKeyword = "%" + keyword + "%";
            for (int i = 0; i < 5; i++) {
                params.add(likeKeyword);
            }
        }
        
        if (category != null && !category.trim().isEmpty()) {
            sql.append(" AND category = ?");
            params.add(category);
        }
        
        if (difficulty != null) {
            sql.append(" AND difficulty = ?");
            params.add(difficulty);
        }
        
        if (teacherId != null) {
            sql.append(" AND teacher_id = ?");
            params.add(teacherId);
        }
        
        sql.append(" ORDER BY create_time DESC");
        
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(Question.class), params.toArray());
    }

}
